package com.wxtf.platform.main.employee;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.*;
/**
 * <li>Title: EmployeeAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.employee</li>
 * <li>Description: 员工基本信息</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author chun_chang
 * @version 1.0.0.0
 * 
 */
public class EmployeeAction extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(EmployeeAction.class);

	public EmployeeAction(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
			} else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("late".equals(this.getMethodName())) {
				msg = this.updatLateData(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if("queryPage_tem".equals(this.getMethodName())){
				msg = this.select_tem(this.getLogicData());
			} else if("listAll".equals(this.getMethodName())) {//子女列表
				msg = this.listAll(this.getLogicData());
			} else if("expData".equals(this.getMethodName())) {//导出
				msg = this.expData(this.getLogicData());
			} else if("query1".equals(this.getMethodName())) {//综合查询
				msg = this.query1(this.getLogicData());
			}else if("gaoji_ser".equals(this.getMethodName())) {
				msg = this.gaoji_ser(this.getLogicData());
			}else if("select_employQuery".equals(this.getMethodName())) {
				msg = this.select_employQuery(this.getLogicData());
			}
			else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(user.getUserrelName(), e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {
			//"tbfi_spouse_name","tbfi_spouse_sex","tbfi_spouse_birthday","tbfi_spouse_idcard","tbfi_spouse_department","tbfi_children_num","tbfi_children_mnum","tbfi_children_fnum",
			"tbe_id","org_id","tbe_idcard","tbe_work_permit",
			"tbe_employment_forms","tbe_name","tbe_former_name","tbe_spell_code","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbe_native_palce",
			"tbe_birthplace","tbe_join_time","tbe_seniority","tbe_family_phone","tbe_mobile","tbe_address","tbe_marital_status","tbe_marital_condition",
			"tbe_married_time","tbe_marriage_certificate","tbe_political_status","tbe_qualification","tbe_graduate_college","tbe_major","tbe_work_area",
			"tbe_position","tbe_is_again_birth","tbe_is_barrenness","the_is_menopause","tbe_is_late_marriage","tbe_is_late_birth","tbe_status","tbe_ba_status",
			"tbe_birth_control_measures","tbe_es_time","tbe_is_es","tbe_es_reason","tbe_children_status","tbe_order","tbe_memo",
			"reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"
	};
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		sql = " from tb_employee e where e.tbe_employment_forms <> '31' ";
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("init".equals(types)) {// 初始查询
				sql += " and e.org_id in (" + user.getCurSubOrgId() + ")";
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String forms = jsonObject.getString("tbe_employment_forms");//用工形式
				String tbe_name = jsonObject.getString("tbe_name");//姓名
				String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证
				String tbe_birth_control_measures = jsonObject.getString("tbe_birth_control_measures");//节育措施
				String tbe_is_late_marriage = jsonObject.getString("tbe_is_late_marriage");//晚婚
				
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and e.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				if(StringUtils.isNotBlank(forms)){
					buff.append(" and e.tbe_employment_forms = '").append(forms).append("'");
				}
				if(StringUtils.isNotBlank(tbe_name)){
					buff.append(" and e.tbe_name like '%").append(tbe_name).append("%'");
				}
				if(StringUtils.isNotBlank(tbe_idcard)){
					buff.append(" and e.tbe_idcard like '%").append(tbe_idcard).append("%'");
				}
				if(StringUtils.isNotBlank(tbe_birth_control_measures)){
					buff.append(" and e.tbe_birth_control_measures = '").append(tbe_birth_control_measures).append("'");
				}
				if(StringUtils.isNotBlank(tbe_is_late_marriage)){
					buff.append(" and e.tbe_is_late_marriage = '").append(tbe_is_late_marriage).append("'");
				}
				sql += buff.toString();
			} else if("serch2".equals(types)) {// 高级查询
				String where = jsonObject.getString("where");//机构ID串
				sql = " FROM ( select * from tb_employee where 1 = 1 " + where + ") e where e.tbe_employment_forms <> '31' and e.org_id in (" + user.getCurSubOrgId() + ")";
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			sql += " order by e." + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select e.tbe_id,e.org_id,e.tbe_idcard,e.tbe_work_permit,e.tbe_employment_forms,e.tbe_name,e.tbe_former_name,e.tbe_spell_code,e.tbe_sex,e.tbe_age,e.tbe_birthday,e.tbe_nation,e.tbe_native_palce,e.tbe_birthplace,e.tbe_join_time,e.tbe_seniority,e.tbe_family_phone,e.tbe_mobile,e.tbe_address,e.tbe_marital_status,e.tbe_marital_condition,e.tbe_married_time,e.tbe_marriage_certificate,e.tbe_political_status,e.tbe_qualification,e.tbe_graduate_college,e.tbe_major,e.tbe_work_area,e.tbe_position,e.tbe_is_again_birth,e.tbe_is_barrenness,e.the_is_menopause,e.tbe_is_late_marriage,e.tbe_is_late_birth,e.tbe_status,e.tbe_ba_status,e.tbe_birth_control_measures,e.tbe_es_time,e.tbe_is_es,e.tbe_es_reason,e.tbe_children_status,e.tbe_order,e.tbe_memo,e.reserve1,e.reserve2,e.reserve3,e.reserve4,e.reserve5,e.create_by,e.create_time,e.last_modify_by,e.last_modify_time " + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	//综合查询
	String[] fields_employQuery = {
			"tbfi_spouse_name","tbfi_spouse_sex","tbfi_spouse_birthday","tbfi_spouse_idcard","tbfi_spouse_department","tbfi_children_num","tbfi_children_mnum","tbfi_children_fnum",
			"tbe_id","org_id","tbe_idcard","tbe_work_permit",
			"tbe_employment_forms","tbe_name","tbe_former_name","tbe_spell_code","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbe_native_palce",
			"tbe_birthplace","tbe_join_time","tbe_seniority","tbe_family_phone","tbe_mobile","tbe_address","tbe_marital_status","tbe_marital_condition",
			"tbe_married_time","tbe_marriage_certificate","tbe_political_status","tbe_qualification","tbe_graduate_college","tbe_major","tbe_work_area",
			"tbe_position","tbe_is_again_birth","tbe_is_barrenness","the_is_menopause","tbe_is_late_marriage","tbe_is_late_birth","tbe_status","tbe_ba_status",
			"tbe_birth_control_measures","tbe_es_time","tbe_is_es","tbe_es_reason","tbe_children_status","tbe_order","tbe_memo",
			"reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"
	};
	public Object select_employQuery(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		sql = " from tb_employee e left join tb_family_base f on e.tbe_id=f.tbe_id where e.tbe_employment_forms <> '31' ";
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("init".equals(types)) {// 初始查询
				sql += " and e.org_id in (" + user.getCurSubOrgId() + ")";
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String forms = jsonObject.getString("tbe_employment_forms");//用工形式
				String tbe_name = jsonObject.getString("tbe_name");//姓名
				String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and e.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				if(StringUtils.isNotBlank(forms)){
					if(!"0".equals(forms)){//全部
						buff.append(" and e.tbe_employment_forms = '").append(forms).append("'");
					}
				}
				if(StringUtils.isNotBlank(tbe_name)){
					buff.append(" and e.tbe_name like '%").append(tbe_name).append("%'");
				}
				if(StringUtils.isNotBlank(tbe_idcard)){
					buff.append(" and e.tbe_idcard like '%").append(tbe_idcard).append("%'");
				}
				sql += buff.toString();
			} else if("serch2".equals(types)) {// 高级查询
				String where = jsonObject.getString("where");//机构ID串
				sql += where;
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			sql += " order by e." + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select f.tbfi_spouse_name,f.tbfi_spouse_sex,f.tbfi_spouse_birthday,f.tbfi_spouse_idcard,f.tbfi_spouse_department,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,e.tbe_id,e.org_id,e.tbe_idcard,e.tbe_work_permit,e.tbe_employment_forms,e.tbe_name,e.tbe_former_name,e.tbe_spell_code,e.tbe_sex,e.tbe_age,e.tbe_birthday,e.tbe_nation,e.tbe_native_palce,e.tbe_birthplace,e.tbe_join_time,e.tbe_seniority,e.tbe_family_phone,e.tbe_mobile,e.tbe_address,e.tbe_marital_status,e.tbe_marital_condition,e.tbe_married_time,e.tbe_marriage_certificate,e.tbe_political_status,e.tbe_qualification,e.tbe_graduate_college,e.tbe_major,e.tbe_work_area,e.tbe_position,e.tbe_is_again_birth,e.tbe_is_barrenness,e.the_is_menopause,e.tbe_is_late_marriage,e.tbe_is_late_birth,e.tbe_status,e.tbe_ba_status,e.tbe_birth_control_measures,e.tbe_es_time,e.tbe_is_es,e.tbe_es_reason,e.tbe_children_status,e.tbe_order,e.tbe_memo,e.reserve1,e.reserve2,e.reserve3,e.reserve4,e.reserve5,e.create_by,e.create_time,e.last_modify_by,e.last_modify_time " + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields_employQuery, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	
	
	//综合查询--高级查询
	public Object gaoji_ser(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		sql = " from tb_employee e left join tb_family_base f on e.tbe_id=f.tbe_id where e.tbe_employment_forms <> '31' ";
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("serch2".equals(types)) {
				sql = " from tb_employee e left join tb_family_base f on f.tbe_id=e.tbe_id left join tbc_surgery_record sishu on sishu.tbe_id=e.tbe_id "
					+" left join tbe_pregnancy_record js1 on js1.tbfi_id=f.tbfi_id "
					+" left join tbe_neonate_main js2 on js2.tbepr_id=js1.tbepr_id "
					+" left join tma_provide_main yj_fa_1 on yj_fa_1.tbe_id=e.tbe_id "
					+" left join tma_provide_detail yj_fa_2 on yj_fa_2.tmapm_id=yj_fa_1.tmapm_id "
					+" left join tma_apply_main yj_sl_1 on yj_sl_1.create_by=e.tbe_id "
					+" left join tma_apply_detail yj_sl_2 on yj_sl_2.tmaam_id=yj_sl_1.tmaam_id "
					+" where e.tbe_employment_forms <> '31' and e.org_id in (" + user.getCurSubOrgId() + ") ";
				String where = jsonObject.getString("where");//机构ID串
				sql += where;
				sql+=" group by e.tbe_id ";
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			sql += " order by e." + sortname + " " + sortorder;
		}
		String countSql = "select count(*) from (select count(e.tbe_id) " + sql+" ) as ttt ";
		String querySql = "select f.tbfi_spouse_name,f.tbfi_spouse_sex,f.tbfi_spouse_birthday,f.tbfi_spouse_idcard,f.tbfi_spouse_department,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,e.tbe_id,e.org_id,e.tbe_idcard,e.tbe_work_permit,e.tbe_employment_forms,e.tbe_name,e.tbe_former_name,e.tbe_spell_code,e.tbe_sex,e.tbe_age,e.tbe_birthday,e.tbe_nation,e.tbe_native_palce,e.tbe_birthplace,e.tbe_join_time,e.tbe_seniority,e.tbe_family_phone,e.tbe_mobile,e.tbe_address,e.tbe_marital_status,e.tbe_marital_condition,e.tbe_married_time,e.tbe_marriage_certificate,e.tbe_political_status,e.tbe_qualification,e.tbe_graduate_college,e.tbe_major,e.tbe_work_area,e.tbe_position,e.tbe_is_again_birth,e.tbe_is_barrenness,e.the_is_menopause,e.tbe_is_late_marriage,e.tbe_is_late_birth,e.tbe_status,e.tbe_ba_status,e.tbe_birth_control_measures,e.tbe_es_time,e.tbe_is_es,e.tbe_es_reason,e.tbe_children_status,e.tbe_order,e.tbe_memo,e.reserve1,e.reserve2,e.reserve3,e.reserve4,e.reserve5,e.create_by,e.create_time,e.last_modify_by,e.last_modify_time " + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields_employQuery, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	
	
	
	public static Map<String, List<Map<String, String>>> selectTmaDictionary() throws Exception {
		String sql_yj_name = "select tmad_id,tmad_name from tma_dictionary where  tmad_status='0'";
		String[] fields = {"tmad_id","tmad_name"};
		List<Map<String, String>> list1=AccessDataBaseUtil.query(fields, sql_yj_name, null);
		Map<String, List<Map<String, String>>> map = new HashMap<String, List<Map<String, String>>>();//结果集
		map.put("yaoju_name", list1);
		return map;
	}
	
	
	/**
	 * 临时用工查询
	 * */
    public Object select_tem(Object jsonArray) throws Exception {
    	UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		sql = " from tb_employee where tbe_employment_forms = '31' ";
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("init".equals(types)) {// 初始查询
				sql += " and org_id in (" + user.getCurSubOrgId() + ")";
			} else if("serch1".equals(types)){// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String tbe_name = jsonObject.getString("tbe_name");//姓名
				String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证
				
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				if(StringUtils.isNotBlank(tbe_name)){
					buff.append(" and tbe_name like '%").append(tbe_name).append("%'");
				}
				if(StringUtils.isNotBlank(tbe_idcard)){
					buff.append(" and tbe_idcard like '%").append(tbe_idcard).append("%'");
				}
				sql += buff.toString();
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		} else {
			sql += " order by tbe_name desc ";
		}
		
		String countSql = "select count(*) " + sql;
		String querySql = "select tbe_id,org_id,tbe_idcard,tbe_work_permit,tbe_employment_forms,tbe_name,tbe_former_name,tbe_spell_code,tbe_sex,tbe_age,tbe_birthday,tbe_nation,tbe_native_palce,tbe_birthplace,tbe_join_time,tbe_seniority,tbe_family_phone,tbe_mobile,tbe_address,tbe_marital_status,tbe_marital_condition,tbe_married_time,tbe_marriage_certificate,tbe_political_status,tbe_qualification,tbe_graduate_college,tbe_major,tbe_work_area,tbe_position,tbe_is_again_birth,tbe_is_barrenness,the_is_menopause,tbe_is_late_marriage,tbe_is_late_birth,tbe_status,tbe_ba_status,tbe_birth_control_measures,tbe_es_time,tbe_is_es,tbe_es_reason,tbe_children_status,tbe_order,tbe_memo,reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time" + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
    }
    
    /** 
     * 描述：query1 员工综合查询
     * @param jsonArray
     * @return
     * @throws Exception
     * @CreateOn Sep 12, 2012  2:57:30 PM
     * @author chun_chang
     */
    public Object query1(Object jsonArray) throws Exception {
    	UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
    	JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbe_id = jsonObject.getString("tbe_id");
		
		Map<String, Object> map = new HashMap<String, Object>();//结果集
		
		//1.配偶及子女情况
		List<Map<String, Object>> list1 = null;
    	String[] field1 = {
    			"tbfi_id", "tbfi_spouse_is_join","tbfi_spouse_name", "tbfi_spouse_sex", "tbfi_spouse_age", "tbfi_spouse_birthday",
				"tbfi_spouse_idcard", "tbfi_spouse_department", "tbfi_spouse_position", "tbfi_children_num",
				"tbfi_children_mnum", "tbfi_children_fnum"
		};
    	sql = "select tbfi_id,tbfi_spouse_is_join,tbfi_spouse_name,tbfi_spouse_sex,tbfi_spouse_age,tbfi_spouse_birthday,tbfi_spouse_idcard,tbfi_spouse_department,tbfi_spouse_position,tbfi_children_num,tbfi_children_mnum,tbfi_children_fnum from tb_family_base where tbe_id = ? ";
    	list1 = AccessDataBaseUtil.query4ListMap(field1, sql, new Object[] { tbe_id });
    	map.put("family", list1);
    	
		//求家庭id
    	String sql_family="select tbfi_id from tb_family_base where tbfi_valid='1' and tbe_id=? ";
    	String fam_id=String.valueOf(AccessDataBaseUtil.query4List(sql_family, new Object[] { tbe_id })).replace("[", "").replace("]", "");
    	if(null == fam_id || "".equals(fam_id)){
    		sql_family="select  tbfi_id from tb_family_base where tbfi_valid='1' and tbe_spouse_id=?  ";
    		fam_id=String.valueOf(AccessDataBaseUtil.query4List(sql_family, new Object[] { tbe_id })).replace("[", "").replace("]", "");
    	}
    	String tbfi_id = fam_id;//家庭ID
    	
    	
    	//2.生育情况-一胎/再生
    	List<Map<String, Object>> list2 = null;
    	sql = "select tbefbr_type,tbefbr_mname,tbefbr_midcard,tbefbr_mdepartment,tbefbr_fname,tbefbr_fidcard,tbefbr_fdepartment,tbefbr_married_time,tbefbr_marriage_certificate,tbefbr_agree_birth_btime,tbefbr_agree_birth_etime from tbe_faa_birth_record where tbfi_id = ?";
    	String[] field2 = {
    			"tbefbr_type", "tbefbr_mname", "tbefbr_midcard", "tbefbr_mdepartment", "tbefbr_fname",
				"tbefbr_fidcard", "tbefbr_fdepartment","tbefbr_married_time","tbefbr_marriage_certificate",
				"tbefbr_agree_birth_btime","tbefbr_agree_birth_etime"
    	};
    	list2 = AccessDataBaseUtil.query4ListMap(field2, sql, new Object[] { tbfi_id });
    	map.put("birth", list2);
    	//独生子女
    	sql = "select tbeocr_child_name,tbeocr_child_sex,tbeocr_child_age,tbeocr_child_birthday,tbeocr_child_idcard,tbeocr_code,tbeocr_time,tbeocr_give_time,if(tbeocr_fee_param = '0','五元',if(tbeocr_fee_param = '1','十元','无')) from tbe_only_child_record where tbfi_id in (select tbfi_id from tb_family_base where tbe_id=? )";
    	if("oracle".equals(user.getDbType())){
    		sql = "select tbeocr_child_name,tbeocr_child_sex,tbeocr_child_age,tbeocr_child_birthday,tbeocr_child_idcard,tbeocr_code,tbeocr_time,tbeocr_give_time,decode(tbeocr_fee_param,'0','五元','1','十元','无') from tbe_only_child_record where tbfi_id in (select tbfi_id from tb_family_base where tbe_id=? )";
    	}
    	
    	String[] field22 = {
    			"tbeocr_child_name", "tbeocr_child_sex", "tbeocr_child_age", "tbeocr_child_birthday",
				"tbeocr_child_idcard","tbeocr_code","tbeocr_time","tbeocr_give_time","tbeocr_fee_param"
    	};
    	list2 = AccessDataBaseUtil.query4ListMap(field22, sql, new Object[] { tbe_id });
    	map.put("onlyChild", list2);
    	
    	//3.奖罚
    	List<Map<String, Object>> list3 =new ArrayList<Map<String, Object>>();
    	//独生子女费
    	sql = "select trpocfm_id,reserve2,trpocfm_child_birthday from trp_only_child_fee_management where  tbfi_id in (select tbfi_id from tb_family_base where tbe_id=? ) "; //trpocfm_child_birthday <= DATE_SUB(curdate(),INTERVAL 0 YEAR) and trpocfm_child_birthday >= DATE_SUB(curdate(),INTERVAL 18 YEAR) and
    	//String[] field3 = { "trpocfm_id","trpocfm_fee_option","trpocfm_child_birthday","reserve2" };
    	List<Object[]> list3_tem =AccessDataBaseUtil.query(sql, new Object[] { tbe_id });
    	double m = this.countMoney(list3_tem);
		Map<String, Object> maps = new HashMap<String, Object>();
		maps.put("onlyChildFee", m);
    	
    	//退休独生子女父母奖励费
    	sql = "select trpocrr_get_money from trp_only_child_retire_reward where tbe_id = ?";
    	List<Object> l = AccessDataBaseUtil.query4List(sql, new Object[]{ tbe_id });
    	if(l.size() > 0){
    		maps.put("rewardFee", l.get(0));
    	} else {
    		maps.put("rewardFee", "0");
    	}
    	
    	//超生抚养费
    	sql = "select trpecp_penalty from trp_exceeds_children_penalty where tbfi_id in (select tbfi_id from tb_family_base where tbe_id=? )";
    	List<Object> l1 = AccessDataBaseUtil.query4List(sql, new Object[]{ tbe_id });
    	if(l1.size() > 0){
    		maps.put("exceedsFee", l1.get(0));
    	} else {
    		maps.put("exceedsFee", "0");
    	}
    	
    	list3.add(maps);
    	map.put("rewardpunishment", list3);
    	
    	// 4.孕产史
    	Map<String, Object> map4 = new HashMap<String, Object>();
    	//怀孕
		List<Map<String, Object>> list4 = new ArrayList<Map<String, Object>>();
		sql = "select tbepr_option,tbepr_ptime from tbe_pregnancy_record where tbfi_id = ?";
		String[] field4 = { "tbepr_option", "tbepr_ptime" };
		List<Map<String, Object>> list41 = AccessDataBaseUtil.query4ListMap(field4, sql, new Object[] { tbfi_id });
		map4.put("baby", list41);
		
		sql = "select s.tbenm_plan_status,s.tbenm_birth_time from tbe_neonate_main s left join tbe_pregnancy_record t on t.tbepr_id=s.tbepr_id where t.tbfi_id = ?";
		String[] field41 = { "tbenm_plan_status", "tbenm_birth_time" };
		List<Map<String, Object>> list42 = AccessDataBaseUtil.query4ListMap(field41, sql, new Object[] { tbfi_id });
		map4.put("newBaby", list42);
		
    	list4.add(map4);
    	map.put("motherhood", list4);
    	
    	//5.避孕史
    	//四术
    	List<Map<String, Object>> list5 = null;
    	sql = "select (select tmgd_name from tm_general_dictionary where tmgd_id = tbcsr_type),tbcsr_place,tbcsr_hospital,tbcsr_doctor,tbcsr_time from tbc_surgery_record where tbe_id = ?";
    	String[] field5 = {
    		"tbcsr_type","tbcsr_place","tbcsr_hospital","tbcsr_doctor","tbcsr_time"
    	};
    	list5 = AccessDataBaseUtil.query4ListMap(field5, sql, new Object[] { tbe_id });
    	map.put("contraception", list5);
    	
    	//6.药具
    	List<Map<String, Object>> list6 = null;
    	sql = "select r.tmad_kind,r.tmad_name,r.tmad_spec,r.tmad_formulation,r.tmad_batch_code,s.tmapd_num,t.tmapm_code,t.tmapm_style,t.tmapm_person,t.tmapm_time from tma_dictionary r,tma_provide_detail s,tma_provide_main t where r.tmad_id = s.tmad_id and s.tmapm_id = t.tmapm_id and t.tbe_id = ?";
    	String[] field6 = {
    			"tmad_kind", "tmad_name", "tmad_spec", "tmad_formulation", "tmad_batch_code", "tmapd_num",
				"tmapm_code", "tmapm_style", "tmapm_person", "tmapm_time"
    	};
    	list6 = AccessDataBaseUtil.query4ListMap(field6, sql, new Object[] { tbe_id });
    	map.put("contraceptive", list6);
    	
    	//7.随访
    	List<Map<String, Object>> list7 = new ArrayList<Map<String, Object>>();
    	Map<String, Object> map7 = new HashMap<String, Object>();
    	//人流随访
    	
    	//药具使用随访
    	
    	sql=" select t1.tmamvm_lmedicine_des,t1.tmamvm_cmedicine_des, t1.org_id,t1.tmamvm_date,t2.tmamvd_time,t1.tmamvm_lmedicine_des,t1.tmamvm_lmedicine_time,t1.tmad_id,t1.tmamvm_change_reason,t1.tmamvm_cmedicine_des,t1.tmad_begin_time,t2.tmamvd_pharmacy_effect,t2.tmamvd_same_pharmacy_des,t2.tmamvd_side_effect_code,t2.tmamvd_side_effect_des,t2.tmamvd_treament,t2.create_by from tma_medicine_vistor_main t1 left join tma_medicine_vistor_detail t2 on t1.tmamvm_id=t2.tmamvm_id   where t1.tbe_id=? ";
    	String[] field7_2 = {
    			"tmamvm_lmedicine_des","tmamvm_cmedicine_des","org_id", "tmamvm_date", "tmamvd_time", "tmamvm_lmedicine_des", "tmamvm_lmedicine_time","tmad_id","tmamvm_change_reason","tmamvm_cmedicine_des","tmad_begin_time","tmamvd_pharmacy_effect","tmamvd_same_pharmacy_des","tmamvd_side_effect_code","tmamvd_side_effect_des","tmamvd_treament","create_by"
    	};
    	List<Map<String, Object>> list72 = AccessDataBaseUtil.query4ListMap(field7_2, sql, new Object[]{tbe_id});
    	map7.put("yaojusuifang", list72);
    	
    	
    	//重点服务对象随访
    	sql = "select t1.tesvr_description,t1.tesvr_vistor,t1.tesvr_time from tes_vistor_record t1 left join tb_family_base f on f.tbfi_id=t1.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_id = ?";
    	String[] field7 = {
    			"tesvr_description", "tesvr_vistor", "tesvr_time"
    	};
    	List<Map<String, Object>> list73 = AccessDataBaseUtil.query4ListMap(field7, sql, new Object[]{tbe_id});
    	map7.put("keySerObj", list73);
    	
    	list7.add(map7);
    	map.put("visit", list7);
    	
    	array = JSONArray.fromObject(map);
    	return array.toString();
    }
    
  
	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		
		String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证号
		String org_id = jsonObject.getString("org_id");//身份证号
		sql = "select count(*) from tb_employee where tbe_idcard = ? and tbe_status in ('102-1','102-2')";
		int cnt = AccessDataBaseUtil.queryForInt(sql, new Object[]{ tbe_idcard });
		if(cnt > 0){
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"操作失败，身份证号重复\"}";
		}
		
		sql = "select levels from t_department where department_id = ?";
		List<Object> list = AccessDataBaseUtil.query4List(sql, new Object[]{ org_id });
		int levels = Integer.valueOf(String.valueOf(list.get(0)));
		if(levels < 2){//根和一级机构不能添加人员
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"操作失败，该机构下不能添加人员\"}";
		}
		
		StringBuffer buff = new StringBuffer();
		StringBuffer buff2 = new StringBuffer();
		buff.append("insert into tb_employee(tbe_id");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j] = jsonObject.getString(fields[j]);
			buff.append(",").append(fields[j]);
		}
		buff.append(") values(");
		obj[0] = AccessDataBaseUtil.getPKUUID();
		
		String curTime = FormatUtil.getCurentDateTime();
		obj[48] = user.getUserId();
		obj[49] = curTime;
		obj[50] = user.getUserId();
		obj[51] = curTime;
		
		for(int i=0; i<obj.length; i++){
			if(i == 9 || i == 15){
				buff2.append(obj[i]).append(",");
			} else if(i == 2){
				buff2.append("'").append(obj[i].toString().toUpperCase()).append("',");
			} else {
				buff2.append("'").append(obj[i]).append("',");
			}
		}
		buff2.deleteCharAt(buff2.length() - 1);
		String sql1 = buff.append(buff2.toString()).append(")").toString();
		
		String sex = "男".equals(obj[8]) ? "女" : "男";
		sql = "insert into tb_family_base(tbfi_id,org_id,tbe_id,tbfi_spouse_sex,tbfi_spouse_is_join,tbfi_children_num,tbfi_children_mnum,tbfi_children_fnum,tbfi_valid,tbfi_phone,tbfi_address,create_by,create_time,last_modify_by,last_modify_time) values('" + AccessDataBaseUtil.getPKUUID() + "','" + obj[1] + "','" + obj[0] + "','" + sex + "','0',0,0,0,1,'" + jsonObject.getString("tbe_family_phone") + "','" + jsonObject.getString("tbe_address") + "','" + obj[48] + "','" + obj[49] + "','" + obj[50] + "','" + obj[51] + "')";
		String[] sqls = { sql1, sql };
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"保存成功\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		
		String tbe_id = jsonObject.getString("tbe_id");//主键
		String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证号
		sql = "select count(*) from tb_employee where tbe_idcard = ? and tbe_id <> ?";
		int cnt = AccessDataBaseUtil.queryForInt(sql, new Object[]{ tbe_idcard, tbe_id });
		if(cnt > 0){
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"操作失败，身份证号重复\"}";
		}
		
		StringBuffer buff = new StringBuffer();
		buff.append("update tb_employee set ");
		buff.append("tbe_idcard = '").append(jsonObject.getString("tbe_idcard").toUpperCase()).append("',")
			.append("tbe_work_permit = '").append(jsonObject.getString("tbe_work_permit")).append("',")
			.append("tbe_employment_forms = '").append(jsonObject.getString("tbe_employment_forms")).append("',")
			.append("tbe_name = '").append(jsonObject.getString("tbe_name")).append("',")
			.append("tbe_former_name = '").append(jsonObject.getString("tbe_former_name")).append("',")
			.append("tbe_spell_code = '").append(jsonObject.getString("tbe_spell_code")).append("',")
			.append("tbe_sex = '").append(jsonObject.getString("tbe_sex")).append("',")
			.append("tbe_age = ").append(jsonObject.getString("tbe_age")).append(",")
			.append("tbe_birthday = '").append(jsonObject.getString("tbe_birthday")).append("',")
			.append("tbe_nation = '").append(jsonObject.getString("tbe_nation")).append("',")
			.append("tbe_native_palce = '").append(jsonObject.getString("tbe_native_palce")).append("',")
			.append("tbe_birthplace = '").append(jsonObject.getString("tbe_birthplace")).append("',")
			.append("tbe_join_time = '").append(jsonObject.getString("tbe_join_time")).append("',")
			.append("tbe_seniority = ").append(jsonObject.getString("tbe_seniority")).append(",")
			.append("tbe_family_phone = '").append(jsonObject.getString("tbe_family_phone")).append("',")
			.append("tbe_mobile = '").append(jsonObject.getString("tbe_mobile")).append("',")
			.append("tbe_address = '").append(jsonObject.getString("tbe_address")).append("',")
			//.append("tbe_marital_status = '").append(jsonObject.getString("tbe_marital_status")).append("',")
			.append("tbe_marital_condition = '").append(jsonObject.getString("tbe_marital_condition")).append("',")
			//.append("tbe_married_time = '").append(jsonObject.getString("tbe_married_time")).append("',")
			//.append("tbe_marriage_certificate = '").append(jsonObject.getString("tbe_marriage_certificate")).append("',")
			.append("tbe_political_status = '").append(jsonObject.getString("tbe_political_status")).append("',")
			.append("tbe_qualification = '").append(jsonObject.getString("tbe_qualification")).append("',")
			.append("tbe_graduate_college = '").append(jsonObject.getString("tbe_graduate_college")).append("',")
			.append("tbe_major = '").append(jsonObject.getString("tbe_major")).append("',")
			.append("tbe_work_area = '").append(jsonObject.getString("tbe_work_area")).append("',")
			.append("tbe_position = '").append(jsonObject.getString("tbe_position")).append("',")
			.append("tbe_is_again_birth = '").append(jsonObject.getString("tbe_is_again_birth")).append("',")
			.append("tbe_is_barrenness = '").append(jsonObject.getString("tbe_is_barrenness")).append("',")
			.append("the_is_menopause = '").append(jsonObject.getString("the_is_menopause")).append("',")
			.append("tbe_is_late_marriage = '").append(jsonObject.getString("tbe_is_late_marriage")).append("',")
			.append("tbe_is_late_birth = '").append(jsonObject.getString("tbe_is_late_birth")).append("',")
			.append("tbe_status = '").append(jsonObject.getString("tbe_status")).append("',")
			.append("tbe_ba_status = '").append(jsonObject.getString("tbe_ba_status")).append("',")
			.append("tbe_birth_control_measures = '").append(jsonObject.getString("tbe_birth_control_measures")).append("',")
			.append("tbe_es_time = '").append(jsonObject.getString("tbe_es_time")).append("',")
			.append("tbe_is_es = '").append(jsonObject.getString("tbe_is_es")).append("',")
			.append("tbe_es_reason = '").append(jsonObject.getString("tbe_es_reason")).append("',")
			.append("tbe_children_status = '").append(jsonObject.getString("tbe_children_status")).append("',")
			.append("tbe_order = ").append(jsonObject.getString("tbe_order")).append(",")
			.append("tbe_memo = '").append(jsonObject.getString("tbe_memo")).append("',")
			.append("reserve1 = '").append(jsonObject.getString("reserve1")).append("',")
			.append("reserve2 = '").append(jsonObject.getString("reserve2")).append("',")
			.append("reserve3 = '").append(jsonObject.getString("reserve3")).append("',")
			.append("reserve4 = '").append(jsonObject.getString("reserve4")).append("',")
			.append("reserve5 = '").append(jsonObject.getString("reserve5")).append("',")
			.append("last_modify_by = '").append(user.getUserId()).append("',")
			.append("last_modify_time = '").append(curTime).append("'")
			.append(" where tbe_id = '").append(jsonObject.getString("tbe_id")).append("'");
		
		StringBuffer buff1 = new StringBuffer();
		buff1.append("update tb_family_base set ")
			.append("tbfi_spouse_name = '").append(jsonObject.getString("tbe_name")).append("',")
			.append("tbfi_spouse_sex = '").append(jsonObject.getString("tbe_sex")).append("',")
			.append("tbfi_spouse_birthday = '").append(jsonObject.getString("tbe_birthday")).append("',")
			.append("tbfi_spouse_idcard = '").append(jsonObject.getString("tbe_idcard")).append("',")
			.append("tbfi_spouse_department = '").append(jsonObject.getString("reserve1")).append("',")
			.append("tbfi_spouse_position = '").append(jsonObject.getString("tbe_position")).append("',")
			.append("tbfi_phone = '").append(jsonObject.getString("tbe_family_phone")).append("',")
			.append("tbfi_address = '").append(jsonObject.getString("tbe_address")).append("'")
			.append(" where tbe_spouse_id = '").append(jsonObject.getString("tbe_id")).append("'");
		
		String[] sqls = { buff.toString(), buff1.toString() };
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		sql = "delete from tb_employee where tbe_id = ?";
		
		AccessDataBaseUtil.deleteByIds(sql, ids);
		return "\"删除成功\"";
	}

	/**
	 * 晚婚+晚育
	 * wangli
	 * */
	public Object updatLateData(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		//String[] ids = id.split(",");
		sql = "update tb_employee set tbe_is_late_birth=1 where tbe_id= "+id;
		String[] sqls = { sql };
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改成功\"";
	}
	
	/** 
	 * 描述：listAll 查看所有的未婚、离异、丧偶人员
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Aug 28, 2012  1:31:49 PM
	 * @author chun_chang
	 */
	public Object listAll(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbe_name = jsonObject.getString("tbe_name");
		String tbe_idcard = jsonObject.getString("tbe_idcard");
		String tbe_sex = jsonObject.getString("tbe_sex");
		//未婚、离异、丧偶或者已婚无配偶信息
		sql = " from tb_employee where tbe_employment_forms <> '31' " +
				" and ( tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')" +
				"	or (tbe_marital_status in ('EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A321') and tbe_id in ( " +
				"		select r.tbe_id from tb_family_base r where (r.tbfi_spouse_name is null or r.tbfi_spouse_name = '') and r.tbe_id  = tbe_id" +
				")))";
		if(StringUtils.isNotBlank(tbe_name)){
			sql += " and tbe_name like '%" + tbe_name + "%'";
		}
		if(StringUtils.isNotBlank(tbe_idcard)){
			sql += " and tbe_idcard like '%" + tbe_idcard + "%'";
		}
		if(StringUtils.isNotBlank(tbe_sex)){
			sql += " and tbe_sex <> '" + tbe_sex + "'";
		}
		
		String countSql = "select count(*)" + sql;
		String querySql = "select tbe_id,tbe_idcard,tbe_name,tbe_sex,tbe_age,tbe_birthday,org_id,reserve1,tbe_spell_code,tbe_position,tbe_qualification" + sql;
		String[] fields = {
			"tbe_id","tbe_idcard","tbe_name","tbe_sex","tbe_age","tbe_birthday","org_id","reserve1","tbe_spell_code","tbe_position","tbe_qualification"
		};
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	
	/** 
	 * 描述：expData 人员导出
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Sep 5, 2012  5:06:00 PM
	 * @author chun_chang
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String orgIds = jsonObject.getString("orgIds");//机构ID串
		String forms = jsonObject.getString("tbe_employment_forms");//用工形式
		String tbe_name = jsonObject.getString("tbe_name");//姓名
		String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证
		
		String tbe_birth_control_measures = jsonObject.getString("tbe_birth_control_measures");//节育措施
		String tbe_is_late_marriage = jsonObject.getString("tbe_is_late_marriage");//晚婚
		
		StringBuffer buff = new StringBuffer();
		buff.append("select reserve1,tbe_idcard,tbe_work_permit,")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_employment_forms),")
			.append("tbe_name,tbe_sex,tbe_birthday,")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_nation),")
			.append("tbe_native_palce,tbe_birthplace,tbe_family_phone,tbe_address,")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_marital_status),")
			.append("tbe_married_time,tbe_marriage_certificate,")
			.append("tbe_work_area,");
		if("mysql".equals(user.getDbType())){
			buff.append("if(tbe_position='1','专业技术管理','生产操作服务'),")
				.append("if(tbe_is_again_birth='1','是','否'),")
				.append("if(tbe_is_barrenness='1','是','否'),")
				.append("if(the_is_menopause='1','是','否'),")
				.append("if(tbe_is_late_marriage='1','是','否'),")
				.append("if(tbe_is_late_birth='1','是','否'),");
		} else if("oracle".equals(user.getDbType())) {
			buff.append("decode(tbe_position,'1','专业技术管理','生产操作服务'),")
				.append("decode(tbe_is_again_birth,'1','是','否'),")
				.append("decode(tbe_is_barrenness,'1','是','否'),")
				.append("decode(the_is_menopause,'1','是','否'),")
				.append("decode(tbe_is_late_marriage,'1','是','否'),")
				.append("decode(tbe_is_late_birth,'1','是','否'),");
		}
		buff.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_status),")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_ba_status),")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_birth_control_measures),");
		if("mysql".equals(user.getDbType())){
			buff.append("if(tbe_is_es='1','是','否'),");
		}else if("oracle".equals(user.getDbType())){
			buff.append("decode(tbe_is_es='1','是','否'),");
		}
		buff.append("tbe_es_time,")
			.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = tbe_es_reason),")
			.append("tbe_children_status,tbe_memo from tb_employee ");
		
		if(StringUtils.isBlank(orgIds)){
			buff.append(" where org_id in (").append(user.getCurSubOrgId()).append(")");
		} else {
			buff.append(" where org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
		}
		if(StringUtils.isNotBlank(forms)) {
			if("0".equals(forms)) {// 全部
				buff.append(" and tbe_employment_forms <> '31'");
			} else {
				buff.append(" and tbe_employment_forms = '").append(forms).append("'");
			}
		}
		if(StringUtils.isNotBlank(tbe_name)) {
			buff.append(" and tbe_name like '%").append(tbe_name).append("%'");
		}
		if(StringUtils.isNotBlank(tbe_idcard)) {
			buff.append(" and tbe_idcard like '%").append(tbe_idcard).append("%'");
		}
		if(StringUtils.isNotBlank(tbe_birth_control_measures)){
			buff.append(" and tbe_birth_control_measures = '").append(tbe_birth_control_measures).append("'");
		}
		if(StringUtils.isNotBlank(tbe_is_late_marriage)){
			buff.append(" and tbe_is_late_marriage = '").append(tbe_is_late_marriage).append("'");
		}
		
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);
		
		
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/employee.xls";
		String file = url + "/file/employee.xls";
		String rfile = "jsp/app/xls/file/employee.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
		
		if(list.size() > 0){
			XLSUtils.writeXls(list, fto, "员工一览表", 2);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		
		return "\"" + rfile + "\"";
	}
	
	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}
	
	/** 
	 * 描述：countMoney 计算一个员工的独生子女费
	 * @param list
	 * @return
	 * @throws Exception
	 * @CreateOn Sep 13, 2012  11:06:51 AM
	 * @author chun_chang
	 */
	private int countMoney(List<Object[]> listtotal) throws Exception{
		int totals=0;
		if (listtotal.size() > 0) {
			for (int i = 0; i < listtotal.size(); i++) {// 行
				int sum=0;
				Object[] ids=listtotal.get(i);
				if(ids[1].toString().equals(""))//该独生子女证有效，需要继续计算
				{
					String sqloptions="Select trpocfm_fee_option,create_time from trp_only_child_fee_options where trpocfm_id = '"+ids[0].toString()+"' order by create_time";
					List<Object[]> listopt = AccessDataBaseUtil.query(sqloptions.toString(), null);
					//最新更新日期与当前时间对比0----1-----2------最新更新-----当前
					for (int j = listopt.size()-1; j >=0 ; j--) {// 行
						int option = 0;
						if(listopt.get(j)[0].equals("五元") || listopt.get(j)[0].equals("0")) {
							option = 5;
						} else if(listopt.get(j)[0].equals("十元") || listopt.get(j)[0].equals("1")) {
							option = 10;
						}
						if(j == listopt.size() - 1) {
							SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
							Date dd = new Date();
							
							//先需要判断独生子女的年龄是否已经满18岁
							if(FormatUtil.remainDateToString((Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4),ft.format(dd))<=0||ft.format(dd).equals((Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4)))
							{
								int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(), ft.format(dd));
								sum += month * option;
								sum = sum<0 ? 0 : sum;
							}
							else
							{
								//直接以出生日期之后的第18年为止
								int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(),(Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4));
								sum += month * option;
								sum = sum<0 ? 0 : sum;
							}

							if(listopt.size()==2)
							{
								if(listopt.get(0)[0].equals("五元") || listopt.get(0)[0].equals("0")) {
									option = 5;
								} else if(listopt.get(0)[0].equals("十元") || listopt.get(0)[0].equals("1")) {
									option = 10;
								}
								int mon = FormatUtil.remainDateToString(listopt.get(0)[1].toString(), listopt.get(j)[1].toString());
								sum += mon * option;
								sum = sum<0 ? 0 : sum;
							}
						}
						if(listopt.size()!=2)
						{
							if(j >= 1) {
								int month = FormatUtil.remainDateToString(listopt.get(j - 1)[1].toString(), listopt.get(j)[1].toString());
								sum += month * option;
							}
						}
					}
					totals += sum;
				}else{totals+=Integer.parseInt(ids[1].toString());}
			}
		}
		return totals;
	}
	
	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	
	
}


